package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import com.model.ProxyIp;

public class MYSQLControl {

    //根据自己的数据库地址修改
    static DataSource ds = MyDataSource.getDataSource("jdbc:mysql://127.0.0.1:3306/test");
    static QueryRunner qr = new QueryRunner(ds);
    
    public static List<ProxyIp> executeQueryAll() throws SQLException{
    	ResultSetHandler<List<ProxyIp>> rsh = new ResultSetHandler<List<ProxyIp>>() {
			@Override
			public List<ProxyIp> handle(ResultSet paramResultSet) throws SQLException {
				List<ProxyIp> list = new ArrayList<ProxyIp>();
				while(paramResultSet.next()){
					ProxyIp ip = new ProxyIp();
					ip.setIp(paramResultSet.getString(1));
					ip.setPort(paramResultSet.getString(2));
					ip.setShowType(paramResultSet.getString(3));
					ip.setRequestType(paramResultSet.getString(4));
					ip.setLocation(paramResultSet.getString(5));
					ip.setResponseTime(paramResultSet.getString(6));
					ip.setLastCheck(paramResultSet.getDate(7));
					list.add(ip);
				}
				return list;
			}
		};
		List<ProxyIp> iplist = qr.query("select ip, port, show_type,request_type,location,response_time,last_check from proxyip", rsh);
		return iplist;
    }
    
    public static ProxyIp executeQuery(String ipstr) throws SQLException{
    	ResultSetHandler<ProxyIp> rsh = new ResultSetHandler<ProxyIp>() {
    		@Override
    		public ProxyIp handle(ResultSet paramResultSet) throws SQLException {
    			ProxyIp ip = new ProxyIp();
    			while(paramResultSet.next()){
    				ip.setIp(paramResultSet.getString(1));
    				ip.setPort(paramResultSet.getString(2));
    				ip.setShowType(paramResultSet.getString(3));
    				ip.setRequestType(paramResultSet.getString(4));
    				ip.setLocation(paramResultSet.getString(5));
    				ip.setResponseTime(paramResultSet.getString(6));
    				ip.setLastCheck(paramResultSet.getDate(7));
    				return ip;
    			}
    			return null;
    		}
    	};
    	ProxyIp ip = null;
    	Object param = ipstr;
    	ip = qr.query("select ip, port, show_type,request_type,location,response_time,last_check from proxyip where ip=?",rsh, param);
    	return ip;
    }
    
    public static void executeInsert(List<ProxyIp> iplist) throws SQLException {
        /*
         * 定义一个Object数组，行列
         * 3表示列数，根据自己的数据定义这里面的数字
         * params[i][0]等是对数组赋值，这里用到集合的get方法
         * 
         */
        Object[][] params = new Object[iplist.size()][7];
        for ( int i=0; i<params.length; i++ ){
        	ProxyIp ip = iplist.get(i);
            params[i][0] = ip.getIp();
            params[i][1] = ip.getPort();
            params[i][2] = ip.getShowType();
            params[i][3] = ip.getRequestType();
            params[i][4] = ip.getLocation();
            params[i][5] = ip.getResponseTime();
            params[i][6] = ip.getLastCheck();
        }
        qr.batch("insert into proxyip (ip, port, show_type,request_type,location,response_time,last_check)"
                + "values (?,?,?,?,?,?,?)", params);

    }
    
    public static void deleteByMainkey(String ip) throws SQLException{
    	qr.execute("delete from proxyip where ip=?", ip);
    }
    
}